﻿CREATE TRIGGER dbo.TRG_TPW_WF_STATE_MACHINE
ON dbo.TPW_WF_STATE_MACHINE
INSTEAD OF INSERT, UPDATE
AS
	IF EXISTS
	(
		SELECT	1
		FROM	INSERTED			I,
				dbo.TPW_WF_STATE	S
		WHERE	S.STATE_ID	= I.STATE_ID_OLD
			AND	(S.ACTIVITY <> I.ACTIVITY OR S.STATE_NAME <> I.STATE_NAME_OLD)
	)
	BEGIN
		RAISERROR(N'ACTIVITY, STATE_ID_OLD and STATE_NAME_OLD mismatch!', 11, 2);
		ROLLBACK TRANSACTION;
		RETURN;
	END;

	IF EXISTS
	(
		SELECT	1
		FROM	INSERTED			I,
				dbo.TPW_WF_EVENT	E
		WHERE	E.EVENT_ID	= I.EVENT_ID
			AND	(E.ACTIVITY <> I.ACTIVITY OR E.EVENT_NAME <> I.EVENT_NAME)
	)
	BEGIN
		RAISERROR(N'ACTIVITY, EVENT_ID and EVENT_NAME mismatch!', 12, 12);
		ROLLBACK TRANSACTION;
		RETURN;
	END;

	IF EXISTS
	(
		SELECT	1
		FROM	INSERTED			I,
				dbo.TPW_WF_STATE	S
		WHERE	S.STATE_ID	= I.STATE_ID_NEW
			AND	(S.ACTIVITY <> I.ACTIVITY OR S.STATE_NAME <> I.STATE_NAME_NEW)
	)
	BEGIN
		RAISERROR(N'ACTIVITY, STATE_ID_NEW and STATE_NAME_NEW mismatch!', 11, 2);
		ROLLBACK TRANSACTION;
		RETURN;
	END;

	DECLARE	@nUpdatedRows	INT;
	
	SELECT	@nUpdatedRows = COUNT(*) FROM DELETED;
	IF @nUpdatedRows > 1
		IF UPDATE(STATE_ID_OLD) OR UPDATE(EVENT_ID)
		BEGIN
			RAISERROR(N'The column belong to primary keys now allow to be changed for more than one row at a time!', 14, 41);
			ROLLBACK TRANSACTION;
			RETURN;
		END;

	DECLARE	@tTableVar	TABLE
	(
		STATE_ID_OLD		SMALLINT,
		EVENT_ID			SMALLINT,
		STATE_ID_NEW		SMALLINT,
		ACTIVITY			NVARCHAR(32),
		STATE_NAME_OLD		NVARCHAR(32),
		EVENT_NAME			NVARCHAR(32),
		STATE_NAME_NEW		NVARCHAR(32),
		DESCRIPTION_		NVARCHAR(256)
	);

	INSERT INTO	@tTableVar (STATE_ID_OLD, EVENT_ID, STATE_ID_NEW, ACTIVITY, STATE_NAME_OLD, EVENT_NAME, STATE_NAME_NEW, DESCRIPTION_)
	SELECT STATE_ID_OLD, EVENT_ID, STATE_ID_NEW, ACTIVITY, STATE_NAME_OLD, EVENT_NAME, STATE_NAME_NEW, DESCRIPTION_ FROM INSERTED;

	UPDATE	T
	SET		ACTIVITY		= S.ACTIVITY,
			STATE_NAME_OLD	= S.STATE_NAME
	FROM	@tTableVar			T
	JOIN	dbo.TPW_WF_STATE	S
	ON		(S.STATE_ID = T.STATE_ID_OLD AND (S.ACTIVITY = T.ACTIVITY OR T.ACTIVITY IS NULL))
	WHERE	T.STATE_NAME_OLD	IS NULL	OR	T.ACTIVITY	IS NULL;

	UPDATE	T
	SET		ACTIVITY		= E.ACTIVITY,
			EVENT_NAME		= E.EVENT_NAME
	FROM	@tTableVar			T
	JOIN	dbo.TPW_WF_EVENT	E
	ON		(E.EVENT_ID = T.EVENT_ID AND (E.ACTIVITY = T.ACTIVITY OR T.ACTIVITY IS NULL))
	WHERE	T.EVENT_NAME	IS NULL		OR	T.ACTIVITY	IS NULL;

	UPDATE	T
	SET		ACTIVITY		= S.ACTIVITY,
			STATE_NAME_NEW	= S.STATE_NAME
	FROM	@tTableVar			T
	JOIN	dbo.TPW_WF_STATE	S
	ON		(S.STATE_ID = T.STATE_ID_NEW AND (S.ACTIVITY = T.ACTIVITY OR T.ACTIVITY IS NULL))
	WHERE	T.STATE_NAME_NEW	IS NULL	OR S.ACTIVITY	IS NULL;

	UPDATE	T
	SET		STATE_ID_OLD	= S.STATE_ID
	FROM	@tTableVar			T
	JOIN	dbo.TPW_WF_STATE	S
	ON		(S.ACTIVITY = T.ACTIVITY AND S.STATE_NAME = T.STATE_NAME_OLD)
	WHERE	T.STATE_ID_OLD	IS NULL;

	UPDATE	T
	SET		EVENT_ID		= E.EVENT_ID
	FROM	@tTableVar			T
	JOIN	dbo.TPW_WF_EVENT	E
	ON		(E.ACTIVITY = T.ACTIVITY AND E.EVENT_NAME = T.EVENT_NAME)
	WHERE	T.EVENT_ID		IS NULL;

	UPDATE	T
	SET		STATE_ID_NEW	= S.STATE_ID
	FROM	@tTableVar			T
	JOIN	dbo.TPW_WF_STATE	S
	ON		(S.ACTIVITY = T.ACTIVITY AND S.STATE_NAME = T.STATE_NAME_NEW)
	WHERE	T.STATE_ID_NEW	IS NULL;

	IF (@nUpdatedRows > 1)
		UPDATE	M
		SET
				STATE_ID_NEW			= T.STATE_ID_NEW,
				ACTIVITY				= T.ACTIVITY,
				STATE_NAME_OLD			= T.STATE_NAME_OLD,
				EVENT_NAME				= T.EVENT_NAME,
				STATE_NAME_NEW			= T.STATE_NAME_NEW,
				DESCRIPTION_			= T.DESCRIPTION_
		FROM	dbo.TPW_WF_STATE_MACHINE	M
		JOIN	@tTableVar					T
		ON		(M.STATE_ID_OLD = T.STATE_ID_OLD AND M.EVENT_ID = T.EVENT_ID);
	ELSE IF (@nUpdatedRows = 1)
		UPDATE	M
		SET
				STATE_ID_OLD			= I.STATE_ID_OLD,
				EVENT_ID				= I.EVENT_ID,
				STATE_ID_NEW			= I.STATE_ID_NEW,
				ACTIVITY				= I.ACTIVITY,
				STATE_NAME_OLD			= I.STATE_NAME_OLD,
				EVENT_NAME				= I.EVENT_NAME,
				STATE_NAME_NEW			= I.STATE_NAME_NEW,
				DESCRIPTION_			= I.DESCRIPTION_
		FROM	dbo.TPW_WF_STATE_MACHINE	M
		INNER JOIN	DELETED					D
		ON		(M.STATE_ID_OLD = D.STATE_ID_OLD AND M.EVENT_ID = D.EVENT_ID)
		CROSS JOIN	@tTableVar			I;
	ELSE
		INSERT	INTO dbo.TPW_WF_STATE_MACHINE (STATE_ID_OLD, EVENT_ID, STATE_ID_NEW, ACTIVITY, STATE_NAME_OLD, EVENT_NAME, STATE_NAME_NEW, DESCRIPTION_)
		SELECT	STATE_ID_OLD, EVENT_ID, STATE_ID_NEW, ACTIVITY, STATE_NAME_OLD, EVENT_NAME, STATE_NAME_NEW, DESCRIPTION_
		FROM	@tTableVar;

----------------------------------------------------------------------------------------------------
--
--	Copyright 2012 Abel Cheng
--	This source code is subject to terms and conditions of the Apache License, Version 2.0.
--	See http://www.apache.org/licenses/LICENSE-2.0.
--	All other rights reserved.
--	You must not remove this notice, or any other, from this software.
--
--	Original Author:	Abel Cheng <abelcys@gmail.com>
--	Created Date:		2012-03-23
--	Primary Host:		http://dbParallel.codeplex.com
--	Change Log:
--	Author				Date			Comment
--
--
--
--
--	(Keep clean code rather than complicated code plus long comments.)
--
----------------------------------------------------------------------------------------------------
